/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.db.mdm.postgresql;

import com.db.mdm.AuditRecord;
import com.db.mdm.MDMAuditThread;
import com.db.mdm.MDMConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author srkrishnan
 */
public class PostgresqlConnection implements MDMConnection {

    String host;
    String user;
    String pass;
    String db;
    String dbClass;
    int port;
    protected String id;
    protected String name;

    Connection con=null;

    public Connection getCon() {
        return con;
    }

    public void setCon(Connection con) {
        this.con = con;
    }



    /**
     * Get the value of name
     *
     * @return the value of name
     */
    public String getName() {
        return name;
    }

    /**
     * Set the value of name
     *
     * @param name new value of name
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * Get the value of id
     *
     * @return the value of id
     */
    public String getId() {
        return id;
    }

    /**
     * Set the value of id
     *
     * @param id new value of id
     */
    public void setId(String id) {
        this.id = id;
    }

    public PostgresqlConnection() {
    }

    public PostgresqlConnection(String name, String host, String user, String pass, String db, int port) {
        this.name = name;
        this.host = host;
        this.user = user;
        this.pass = pass;
        this.db = db;
        this.port = port;
    }

    public String getDb() {
        return db;
    }

    public void setDb(String db) {
        this.db = db;
    }

    public String getHost() {
        return host;
    }

    public void setHost(String host) {
        this.host = host;
    }

    public String getPass() {
        return pass;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

    public int getPort() {
        return port;
    }

    public void setPort(int port) {
        this.port = port;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getDbClass() {
        return dbClass;
    }

    public void setDbClass(String dbClass) {
        this.dbClass = dbClass;
    }

    public Connection connectDB() {
        String dbUrl = "jdbc:postgresql://" + host + ":" + port + "/" + db + "";
        
        try {
            Class.forName("org.postgresql.Driver");

            if (con == null || con.isClosed()) {
                con = DriverManager.getConnection(dbUrl, user, pass);
            }

            if (!con.isClosed()) {
                System.out.println("Connected to " + dbUrl + " successfully.....");
            }
            return con;
        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(PostgresqlConnection.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            Logger.getLogger(PostgresqlConnection.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public void createTriggers(MDMConnection auditConnection) {
        createInsertTrigger(auditConnection);
        createUpdateTrigger(auditConnection);
        createDeleteTrigger(auditConnection);
    }

    public void createInsertTrigger(MDMConnection auditConnection) {
        Connection conn=null;
        try {
            /*Get the Connection to database*/
            conn=this.getCon();

            /*create audit tables*/
            ResultSet rsTable = conn.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

            /*create Prepared Statement*/
            PreparedStatement prpsmt;

            /*iterate through all tables*/
            while (rsTable.next()) {
                System.out.println(rsTable.getString(3));
                /*Get all column names from that table*/
                prpsmt = conn.prepareStatement("SELECT column_name,data_type FROM information_schema.columns WHERE table_name= " + rsTable.getString(3));
                ResultSet rsColumn = prpsmt.executeQuery();

                /*build query string for TRIGGER*/
                String droptriggerInsertStr = "DROP TRIGGER IF EXISTS" + "`" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_insert`" + ";";
                String triggerInsertStr = "CREATE TRIGGER `" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_insert` AFTER INSERT ON " + rsTable.getString(3) + " FOR EACH ROW BEGIN";
                triggerInsertStr += " insert into " + ((PostgresqlConnection) auditConnection).getDb() + ".AUD_" + rsTable.getString(3) + " (";

                /*temp string insert into values for trigger*/
                String triggertmp = "";

                /*Iterate through all columns and build query to create audit table*/
                while (rsColumn.next()) {
                    System.out.println(rsColumn.getString(1) + " " + rsColumn.getString(2) + " " + rsColumn.getString(3) + " " + rsColumn.getString(4));
                    triggerInsertStr += "`" + rsColumn.getString(1) + "`,";
                    triggertmp += "new." + "`" + rsColumn.getString(1) + "`,";
                }

                /*add trigger flag and id to trigger query*/
                triggerInsertStr += "`AUD_trigflag`,`AUD_id`";
                triggertmp += "'insert',UUID()";

                /*drop trigger if exists*/
                prpsmt = conn.prepareStatement(droptriggerInsertStr);
                prpsmt.executeUpdate();
                /*Execute query to create insert trigger*/
                prpsmt = conn.prepareStatement(triggerInsertStr + ") VALUES (" + triggertmp + ");END;");
                prpsmt.executeUpdate();
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        } 
    }

    public void createUpdateTrigger(MDMConnection auditConnection) {
        Connection conn=null;
        try {
            /*Get the connection to database*/
            conn=conn=this.getCon();

            /*create audit tables*/
            ResultSet rsTable = conn.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

            /*create Prepared Statement*/
            PreparedStatement prpsmt;

            /*iterate through all tables*/
            while (rsTable.next()) {
                System.out.println(rsTable.getString(3));
                /*Get all column names from that table*/
                prpsmt = conn.prepareStatement("SHOW columns FROM " + rsTable.getString(3));
                ResultSet rsColumn = prpsmt.executeQuery();

                /*build query string for TRIGGER*/
                String droptriggerInsertStr = "DROP TRIGGER IF EXISTS" + "`" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_update`" + ";";
                String triggerInsertStr = "CREATE TRIGGER `" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_update` AFTER UPDATE ON " + rsTable.getString(3) + " FOR EACH ROW BEGIN";
                triggerInsertStr += " insert into " + ((PostgresqlConnection) auditConnection).getDb() + ".AUD_" + rsTable.getString(3) + " (";

                /*temp string insert into values for trigger*/
                String triggertmp = "";

                /*Iterate through all columns and build query to create audit table*/
                while (rsColumn.next()) {
                    System.out.println(rsColumn.getString(1) + " " + rsColumn.getString(2) + " " + rsColumn.getString(3) + " " + rsColumn.getString(4));
                    triggerInsertStr += "`" + rsColumn.getString(1) + "`,";
                    triggertmp += "new." + "`" + rsColumn.getString(1) + "`,";
                }

                /*add trigger flag and id to trigger query*/
                triggerInsertStr += "`AUD_trigflag`,`AUD_id`";
                triggertmp += "'update',UUID()";

                /*drop trigger if exists*/
                prpsmt = conn.prepareStatement(droptriggerInsertStr);
                prpsmt.executeUpdate();
                /*Execute query to create insert trigger*/
                prpsmt =conn.prepareStatement(triggerInsertStr + ") VALUES (" + triggertmp + ");END;");
                prpsmt.executeUpdate();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

    }

    public void createDeleteTrigger(MDMConnection auditConnection) {
       Connection conn=null;
        try {
            /*Get the connection to database*/
            conn=conn=this.getCon();

            /*create audit tables*/
            ResultSet rsTable = conn.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

            /*create Prepared Statement*/
            PreparedStatement prpsmt;

            /*iterate through all tables*/
            while (rsTable.next()) {
                System.out.println(rsTable.getString(3));
                /*Get all column names from that table*/
                prpsmt = conn.prepareStatement("SHOW columns FROM " + rsTable.getString(3));
                ResultSet rsColumn = prpsmt.executeQuery();

                /*build query string for TRIGGER*/
                String droptriggerInsertStr = "DROP TRIGGER IF EXISTS" + "`" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_delete`" + ";";
                String triggerInsertStr = "CREATE TRIGGER `" + rsTable.getString(1) + "_" + rsTable.getString(3) + "_delete` AFTER DELETE ON " + rsTable.getString(3) + " FOR EACH ROW BEGIN";
                triggerInsertStr += " insert into " + ((PostgresqlConnection) auditConnection).getDb() + ".AUD_" + rsTable.getString(3) + " (";

                /*temp string insert into values for trigger*/
                String triggertmp = "";

                /*Iterate through all columns and build query to create audit table*/
                while (rsColumn.next()) {
                    System.out.println(rsColumn.getString(1) + " " + rsColumn.getString(2) + " " + rsColumn.getString(3) + " " + rsColumn.getString(4));
                    triggerInsertStr += "`" + rsColumn.getString(1) + "`,";
                    triggertmp += "old." + "`" + rsColumn.getString(1) + "`,";
                }

                /*add trigger flag and id to trigger query*/
                triggerInsertStr += "`AUD_trigflag`,`AUD_id`";
                triggertmp += "'delete',UUID()";

                /*drop trigger if exists*/
                prpsmt = conn.prepareStatement(droptriggerInsertStr);
                prpsmt.executeUpdate();
                /*Execute query to create insert trigger*/
                prpsmt = conn.prepareStatement(triggerInsertStr + ") VALUES (" + triggertmp + ");END;");
                prpsmt.executeUpdate();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

    }

    public void createRepoTables(MDMConnection repoMdmconnection) {
        Connection conn = null;
        try {
            /*Get the connection to database*/
            conn=conn=this.getCon();

            /*create repository table*/
            PreparedStatement prpsmt = conn.prepareStatement("CREATE TABLE IF NOT EXISTS `repotable` (  `id` varchar(50) NOT NULL,  `db` varchar(20) DEFAULT NULL,  `xml` varchar(500) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
            prpsmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 
    }

    public void createAuditTables(MDMConnection auditMdmConnection, MDMConnection repoMdmConnection) {
        Connection conn = null;
        Connection auditcon=null;
        try {
            /*Get the connection to database*/
            conn=conn=this.getCon();

            

            /*connect to audit database*/
             auditcon= (Connection) auditMdmConnection.getCon();
            /*create audit tables*/
            ResultSet rsTable = conn.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

            /*create Prepared Statement*/
            PreparedStatement prpsmt;



            /*iterate through all tables*/
            while (rsTable.next()) {
                System.out.println(rsTable.getString(3));
                /*Get all column names from that table*/
                prpsmt = conn.prepareStatement("SELECT column_name,data_type FROM information_schema.columns WHERE table_name='" + rsTable.getString(3)+"'");
                ResultSet rsColumn = prpsmt.executeQuery();

                /*drop audit table if already exists*/
                prpsmt=auditcon.prepareStatement("DROP TABLE IF EXISTS "+"\"AUD_" + rsTable.getString(3) + "\"");
                prpsmt.executeUpdate();

                /*build query string to create audit table*/
                String querystr = "CREATE TABLE " + "\"AUD_" + rsTable.getString(3) + "\" (";

                /*Iterate through all columns and build query to create audit table*/
                while (rsColumn.next()) {
                    System.out.println(rsColumn.getString(1) + " " + rsColumn.getString(2) );
                    querystr += "\"" + rsColumn.getString(1) + "\" " + rsColumn.getString(2) + ",";
                }

                /*add trigger flag and id to audit table*/
                querystr += "\"AUD_trigflag\" text,\"AUD_id\" text)";

                /*Execute query to create audit table*/
                prpsmt = auditcon.prepareStatement(querystr);
                prpsmt.executeUpdate();

                /*start Audit Thread*/
                MDMAuditThread auditMDMThread = new MDMAuditThread(auditMdmConnection, repoMdmConnection, "AUD_" + rsTable.getString(3));
                Thread auditThread = new Thread(auditMDMThread, "AUD_" + rsTable.getString(3));
                ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
                scheduler.scheduleWithFixedDelay(auditThread, 0, 300, TimeUnit.MILLISECONDS);
            }
        } catch (SQLException ex) {

            ex.printStackTrace();
            Logger.getLogger(PostgresqlConnection.class.getName()).log(Level.SEVERE, null, ex);

        } 

    }

    public AuditRecord getAuditRecordinXML(MDMConnection auditMdmConnection, String TableName) {
        String dbxml = "";
        PreparedStatement prpsmt = null;
        ResultSet rs;
        AuditRecord auditrecord = new AuditRecord();
        Connection conn = null;
        try {

            /*Get the connection to database*/
            conn=conn=this.getCon();

            /*get data from audit table*/
            prpsmt = conn.prepareStatement("SELECT * FROM \"" + TableName+"\"");
            rs = prpsmt.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();

            if (rs.next()) {
                dbxml = "";
                dbxml += "<db><table>" + md.getTableName(1) + "</table>" + "<record><id>" + rs.getString("AUD_id") + "</id><trigflag>" + rs.getString("AUD_trigflag") + "</trigflag><data>";
                for (int i = 1; i <= columnCount; i++) {
                    if (!md.getColumnName(i).equals("AUD_id") && !md.getColumnName(i).equals("AUD_trigflag")) {
                        dbxml += "<column><name>" + md.getColumnName(i) + "</name><value>" + rs.getString(i) + "</value></column>";
                    }
                }
                dbxml += "</data></record></db>";
                auditrecord.setId(rs.getString("AUD_id"));
                prpsmt = conn.prepareStatement("DELETE FROM \"" + TableName + "\" WHERE \"AUD_id\"='" + rs.getString("AUD_id") + "'");
                prpsmt.executeUpdate();
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } 
        auditrecord.setDbxml(dbxml);
        return auditrecord;
    }

    public void insertAuditRecordintoRepo(MDMConnection repoConnection, AuditRecord auditrecord) {
        Connection conn=null;
        try {
            /*Get the connection to  database*/
            conn = (Connection) repoConnection.getCon();
            System.out.println("Insert record " + auditrecord.getId() + " in repo.");
            PreparedStatement prpsmt = conn.prepareStatement("INSERT INTO repotable (id,db,xml) VALUES (?,?,?)");
            prpsmt.setString(1, auditrecord.getId());
            prpsmt.setString(2, auditrecord.getName());
            prpsmt.setString(3, auditrecord.getDbxml());
            prpsmt.execute();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 
    }



}
